﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace LogisticsDAL
{
    public class CarrierDAL
    {
        /// <summary>
        /// 承运车队
        /// </summary>
        /// <returns></returns>
        public SqlDataReader rpttruck()
        {
            string sql = "p_Carrertteam";
            SqlParameter[] pram =
            {

            };
            return DBHelper.Exreader(sql, pram);
        }

        public DataTable rpttrucks(string table,string primary,int pageindex,int pagesize,out int Count,string TeamName,string Leader)
        {
            string Filer = " 1=1 ";
            if (TeamName != "0")
            {
                Filer = " " + Filer + " and TeamName='" + TeamName + " '";
            }
            if (!string.IsNullOrWhiteSpace(Leader))
            {
                Filer = Filer+" and Leader='" + Leader + " ' ";
            }
            DataTable read = PROC_Helper.Paging(table,primary,pageindex,pagesize,Filer,out Count);
            return read;
        }
     

        /// <summary>
        /// 查询承运车队
        /// </summary>
        /// <param name="teamname"></param>
        /// <param name="leader"></param>
        /// <returns></returns>
        public List<TruckTeam> Teamread(string teamname,string leader)
        {
            StringBuilder sql =new StringBuilder("SELECT  [TeamID],[TeamName],[Leader],[Remark],[CheckInTime],[IsDelete],AlertTime FROM [TruckTeam]");
            List<string> wherelist = new List<string>();
            List<SqlParameter> pramlist = new List<SqlParameter>();
            //wherelist.Add("IsDelete=@isdel");
            //pramlist.Add(new SqlParameter("@isdel", 1));
            List<TruckTeam> tru = new List<TruckTeam>();
            if (teamname !="0"){
                wherelist.Add("TeamName=@Teamname");
                pramlist.Add(new SqlParameter("@Teamname", teamname));
            }
            if (!string.IsNullOrWhiteSpace(leader))
            {
                wherelist.Add("Leader=@Leader");
                pramlist.Add(new SqlParameter("@Leader", leader));
            }
            if (wherelist.Count > 0)
            {
                string str = string.Join(" and ", wherelist.ToArray());
                sql.Append(" where "+str);
            }

            DataTable dt = DBHelper.GetTable(sql.ToString(),pramlist.ToArray());
            if (dt.Rows.Count>0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    tru.Add(new TruckTeam()
                    {
                        TeamID = Convert.ToInt32(item["TeamID"]),
                        TeamName = item["TeamName"].ToString(),
                        Leader = item["Leader"].ToString(),
                        Remark = item["Remark"].ToString(),
                        CheckInTime =Convert.ToDateTime(item["CheckInTime"]),
                        AlertTime= Convert.ToDateTime(item["AlertTime"])
                    });
                }
            }
            return tru;
        }


        /// <summary>
        /// 承运车辆查询
        /// </summary>
        /// <param name="trus"></param>
        /// <returns></returns>
        

        public List<Trucks> BindTruckLister(Trucks trus,out int Recount)
        {
            Recount = 0;
            string sqlproc = "p_CarrersTruck";
            SqlParameter[] pram =
            {
                new SqlParameter("@teamID",SqlDbType.Int),
                new SqlParameter("@Number",SqlDbType.VarChar,50),
                new SqlParameter("@type",SqlDbType.VarChar,50),
                new SqlParameter("@State",SqlDbType.Int),
                new SqlParameter("@PageIndex",SqlDbType.Int),
                new SqlParameter("@PageSize ",SqlDbType.Int),
                new SqlParameter("@PageCount",ParameterDirection.Output),
            };
            pram[0].Value = trus.TeamID;
            pram[1].Value = trus.Number;
            pram[2].Value = trus.Type;
            pram[3].Value = trus.State;
            pram[4].Value = trus.pageIndex;
            pram[5].Value = trus.pageSize;
            pram[6].Direction = ParameterDirection.Output;
            List<Trucks> tru = new List<Trucks>();
            SqlDataReader read = DBHelper.Exreader(sqlproc, pram);
            if (read.HasRows)
            {
                while (read.Read())
                {
                    Trucks ts = new Trucks();
                    ts.TeamName = read["TeamName"].ToString();
                    ts.TruckID = Convert.ToInt32(read["TruckID"]);
                    ts.Number = read["Number"].ToString();
                    ts.Remark = read["Remark"].ToString();
                    ts.Length = read["Length"].ToString();
                    ts.Tonnage = read["Tonnage"].ToString();
                    ts.BuyDate = Convert.ToDateTime(read["BuyDate"]);
                    ts.CheckInTime = Convert.ToDateTime(read["CheckInTime"]);
                    ts.AlterTime = Convert.ToDateTime(read["AlterTime"]);
                    ts.Type = read["Type"].ToString();
                    ts.State = Convert.ToInt32(read["State"]);
                    tru.Add(ts);
                }
                read.Close();
                Recount = Convert.ToInt32(pram[6].Value);
            }
           
            return tru;

        }


        public SqlDataReader ddlTeamName()
        {
            string sql = "select TeamName,TeamID from TruckTeam";
            SqlParameter[] pram = { };
            return DBHelper.getExreaders(sql,pram);
        }

        public SqlDataReader ddlType()
        {
            string sql = "select distinct Type from Truck";
            SqlParameter[] pram = {  };
            return DBHelper.getExreaders(sql, pram);
        }

        public SqlDataReader bindCarries()
        {
            string sql = "p_Carriesold";
            SqlParameter[] pram =
            {

            };
            return DBHelper.Exreader(sql, pram);
        }


        /// <summary>
        /// 历史承运单条件查询（存储过程写到）
        /// </summary>
        /// <param name="crs"></param>
        /// <returns></returns>
        public List<Carriers> SelectCarries(Carriers crs)
        {
            string sql = "P_CarrriesOldSelect";
            List<SqlParameter> sqlpram = new List<SqlParameter>();
            if (!string.IsNullOrWhiteSpace(crs.CarriesID))
            {
                sqlpram.Add(new SqlParameter("@CarriesID", crs.CarriesID));
            }
            if(crs.LeaverDate!="" || crs.LeaverEndDate != "")
            {
                sqlpram.Add(new SqlParameter("@LeaverDate", crs.LeaverDate));
                sqlpram.Add(new SqlParameter("@LeaverEndDate", crs.LeaverEndDate));
            }
            if (!string.IsNullOrWhiteSpace(crs.SendLinkman))
            {
                sqlpram.Add(new SqlParameter("@SendLinkman", crs.SendLinkman));
            }
            if(crs.ReceiveDate!="" || crs.ReceiveEndDate != "")
            {
                sqlpram.Add(new SqlParameter("@ReceiveDate", crs.ReceiveDate));
                sqlpram.Add(new SqlParameter("@ReceiveEndDate", crs.ReceiveEndDate));
            }
            if (!string.IsNullOrWhiteSpace(crs.ReceiveLinkman))
            {
                sqlpram.Add(new SqlParameter("@ReceiveLinkman", crs.ReceiveLinkman));
            }
            if (!string.IsNullOrWhiteSpace(crs.UserName))
            {
                sqlpram.Add(new SqlParameter("@UserName", crs.UserName));
            }
            SqlDataReader read = DBHelper.Exreader(sql, sqlpram.ToArray());
            List<Carriers> cars = new List<Carriers>();
            if (read.HasRows)
            {
                while (read.Read())
                {
                    Carriers cers = new Carriers();
                    cers.CarriesID = read["CarriesID"].ToString();
                    cers.LeaverDate = read["LeaverDate"].ToString();
                    cers.ReceiveDate = read["ReceiveDate"].ToString();
                    cers.zhiDate = read["LeaverDate"].ToString();
                    cers.SendLinkman = read["SendLinkman"].ToString();
                    cers.SendCompany = read["SendCompany"].ToString();
                    cers.SendAddress = read["SendAddress"].ToString();
                    cers.ReceiveLinkman = read["ReceiveLinkman"].ToString();
                    cers.ReceiveCompany = read["ReceiveCompany"].ToString();
                    cers.ReceiveAddress = read["FK_ReceiveAddress"].ToString();
                    cers.TotalCost = read["TotalCost"].ToString();
                    cers.FinishedState = Convert.ToInt32(read["FinishedState"]);
                    cers.UserName = read["UserName"].ToString();
                    cars.Add(cers);
                }
                read.Close();
            }
            return cars;
        }


        public List<Carriers> SelectCarris(Carriers crs,out int ResCount)
        {
            ResCount = 0;
            string sql = "UP_Pagination";
            SqlParameter[] pram =
            {
                new SqlParameter("@Tables",SqlDbType.VarChar,1000),
                new SqlParameter("@PrimaryKey",SqlDbType.VarChar,1000),
                new SqlParameter("@Sort",SqlDbType.VarChar,1000),
                new SqlParameter("@CurrentPage",SqlDbType.Int),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@Fields",SqlDbType.VarChar,1000),
                new SqlParameter("@Filter",SqlDbType.VarChar,1000),
                new SqlParameter("@Group",SqlDbType.VarChar,1000),
                new SqlParameter("@TotalRecordCount",ParameterDirection.Output)
            };
            pram[0].Value = "View_CarriesINuserInfo";
            pram[1].Value = "CarriesID";
            pram[2].Value = "";
            pram[3].Value = crs.pageIndex;
            pram[4].Value = crs.pageSize;
            pram[5].Value = "*";
            string Filter = "1=1";
            if (!string.IsNullOrWhiteSpace(crs.CarriesID))
            {
                Filter = " " + Filter + " and CarriesID='" + crs.CarriesID + " '";
            }
            if (crs.LeaverDate != "" || crs.LeaverEndDate != "")
            {
                Filter = Filter + " and LeaverDate>='" + crs.LeaverDate + " ' and LeaverDate<='" +crs.LeaverEndDate+"'";
            }
            if (!string.IsNullOrWhiteSpace(crs.SendLinkman))
            {
                Filter = " " + Filter + " and SendLinkman='" + crs.SendLinkman + " '";
            }
            if (crs.ReceiveDate != "" || crs.ReceiveEndDate != "")
            {
                Filter = Filter + " and ReceiveDate>='" + crs.ReceiveDate + " ' and ReceiveDate<='" + crs.ReceiveEndDate + "'";
            }
            if (!string.IsNullOrWhiteSpace(crs.ReceiveLinkman))
            {
                Filter = " " + Filter + " and ReceiveLinkman='" + crs.ReceiveLinkman + " '";
            }
            if (!string.IsNullOrWhiteSpace(crs.UserName))
            {
                Filter = " " + Filter + " and UserName='" + crs.UserName + " '";
            }
           
            pram[6].Value = Filter;
            pram[7].Value = "";
            pram[8].Direction = ParameterDirection.Output;
            SqlDataReader read = DBHelper.Exreader(sql, pram);
            List<Carriers> cars = new List<Carriers>();
            if (read.HasRows)
            {
                while (read.Read())
                {
                    Carriers cers = new Carriers();
                    cers.CarriesID = read["CarriesID"].ToString();
                    cers.LeaverDate = read["LeaverDate"].ToString();
                    cers.ReceiveDate = read["ReceiveDate"].ToString();
                    cers.zhiDate = read["LeaverDate"].ToString();
                    cers.SendLinkman = read["SendLinkman"].ToString();
                    cers.SendCompany = read["SendCompany"].ToString();
                    cers.SendAddress = read["SendAddress"].ToString();
                    cers.ReceiveLinkman = read["ReceiveLinkman"].ToString();
                    cers.ReceiveCompany = read["ReceiveCompany"].ToString();
                    cers.ReceiveAddress = read["FK_ReceiveAddress"].ToString();
                    cers.TotalCost = read["TotalCost"].ToString();
                    cers.FinishedState = Convert.ToInt32(read["FinishedState"]);
                    cers.UserName = read["UserName"].ToString();
                    cars.Add(cers);
                }
                read.Close();
                ResCount = Convert.ToInt32(pram[8].Value);
            }
            return cars;
        }
    }
}
